﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

using ZhimeiSoft.CharmManager.DOL;
using ZhimeiSoft.Framework.Server.Base;
using ZhimeiSoft.CharmManager.Server.DAL.Interface;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class ScheduleDAL : BaseDal, IScheduleDAL
    {
        #region IScheuleDAL 成员
        /// <summary>
        /// 插入信息
        /// </summary>
        /// <param name="scheduleInfo"></param>
        public void InsertScheduleCustom(DBEntitySchedule scheduleInfo, DBEntityScheduleCustom scheduleCustom)
        {
            DbCommand cmdInsert = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_INSERT_SCHEDULE);
            DB.AddInParameter(cmdInsert, "SALONID", DbType.Int32, scheduleInfo.SALONID);
            DB.AddInParameter(cmdInsert, "SCHEDULENAME", DbType.String, scheduleInfo.SCHEDULENAME);
            DB.AddInParameter(cmdInsert, "SCHEDULETIME", DbType.DateTime, scheduleInfo.SCHEDULETIME);
            DB.AddInParameter(cmdInsert, "SYSTIME", DbType.DateTime, scheduleInfo.SYSTIME);
            DB.AddInParameter(cmdInsert, "STATE", DbType.String, scheduleInfo.STATE);
            DB.AddInParameter(cmdInsert, "SPLANSTARTTIME", DbType.DateTime, scheduleInfo.SPLANSTARTTIME);
            DB.AddInParameter(cmdInsert, "SPLANENDTIME", DbType.DateTime, scheduleInfo.SPLANENDTIME);
            DB.AddInParameter(cmdInsert, "SPLANTIMESTATE", DbType.String, scheduleInfo.SPLANTIMESTATE);
            DB.AddInParameter(cmdInsert, "SPLANMEMO", DbType.String, scheduleInfo.SPLANMEMO);
            DB.AddInParameter(cmdInsert, "SCHEDULETYPE", DbType.String, scheduleInfo.SCHEDULETYPE);
            DB.AddInParameter(cmdInsert, "SPLANEMPLOYEE", DbType.String, scheduleInfo.SPLANEMPLOYEE);
            DB.ExecuteNonQuery(cmdInsert);
            
            if (scheduleCustom != null)
            {
                DataSet dsScheduleInfo = QueryScheduleGetScheduleID(scheduleInfo);
                if (dsScheduleInfo.Tables[0].Rows.Count != 0)
                {
                    int scheduleID = Convert.ToInt32(dsScheduleInfo.Tables[0].Rows[0]["SCHEDUEID"]);
                    scheduleCustom.SCHEDUEID = scheduleID;
                    InsertScheduleScheduleCustom(scheduleCustom);
                }

            }
        }

        private void InsertScheduleScheduleCustom(DBEntityScheduleCustom scheduleCustom)
        {
            //@CUSTOMID, @SCALLDATE, @SMEMO, @SCHEDULEID
            DbCommand cmdInsert = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_INSERT_SCHEDULECUSTOM);
            DB.AddInParameter(cmdInsert, "CUSTOMID", DbType.Int32, scheduleCustom.CUSTOMID);
            DB.AddInParameter(cmdInsert, "SCALLDATE", DbType.DateTime, scheduleCustom.SCALLDATE);
            DB.AddInParameter(cmdInsert, "SMEMO", DbType.String, scheduleCustom.SMEMO);
            DB.AddInParameter(cmdInsert, "SCHEDULEID", DbType.Int32, scheduleCustom.SCHEDUEID);
            DB.AddInParameter(cmdInsert, "SBESPEAKSTATE", DbType.String, scheduleCustom.SBESPEAKSTATE);
            DB.ExecuteNonQuery(cmdInsert);
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="scheduleInfo"></param>
        public void DeleteScheduleCustom(DBEntitySchedule scheduleInfo, DBEntityScheduleCustom scheduleCustom)
        {
            if (scheduleCustom != null)
            {
                DeleteScheduleScheduleCustom(scheduleCustom);
            }
            DbCommand cmdDelete = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_DELETE_SCHEDULE);
            DB.AddInParameter(cmdDelete, "SCHEDUEID", DbType.Int32, scheduleInfo.SCHEDUEID);
            DB.ExecuteNonQuery(cmdDelete);
        }
        /// <summary>
        /// 删除用户提醒信息
        /// </summary>
        /// <param name="scheduleCustom"></param>
        private void DeleteScheduleScheduleCustom(DBEntityScheduleCustom scheduleCustom){
            DbCommand cmdDelete = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_DEL_SCHEDULECUSTOM);
            DB.AddInParameter(cmdDelete, "CUSTOMID", DbType.Int32, scheduleCustom.CUSTOMID);
            DB.AddInParameter(cmdDelete, "SCHEDULEID", DbType.Int32, scheduleCustom.SCHEDUEID);
            DB.AddInParameter(cmdDelete, "SCALLDATE", DbType.DateTime, scheduleCustom.SCALLDATE);
            DB.AddInParameter(cmdDelete, "SBESPEAKSTATE", DbType.String, scheduleCustom.SBESPEAKSTATE);
            DB.ExecuteNonQuery(cmdDelete);
        }
        /// <summary>
        /// 更新预约信息
        /// </summary>
        /// <param name="scheduleInfo">提醒信息</param>
        /// <param name="scheduleCustomOld">旧的预约信息</param>
        /// <param name="scheduleCustomNew">新的预约信息</param>
        public void UpdateScheduleCustom(DBEntitySchedule scheduleInfo, DBEntityScheduleCustom scheduleCustomOld,
            DBEntityScheduleCustom scheduleCustomNew)
        {
            DbCommand cmdUpdate = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_UPDATE_SCHEDULE);
            DB.AddInParameter(cmdUpdate, "SALONID_NEW", DbType.Int32, scheduleInfo.SALONID);
            DB.AddInParameter(cmdUpdate, "SCHEDULENAME_NEW", DbType.String, scheduleInfo.SCHEDULENAME);
            DB.AddInParameter(cmdUpdate, "SCHEDULETIME_NEW", DbType.DateTime, scheduleInfo.SCHEDULETIME);
            DB.AddInParameter(cmdUpdate, "SYSTIME_NEW", DbType.DateTime, scheduleInfo.SYSTIME);
            DB.AddInParameter(cmdUpdate, "STATE_NEW", DbType.String, scheduleInfo.STATE);
            DB.AddInParameter(cmdUpdate, "SPLANSTARTTIME_NEW", DbType.DateTime, scheduleInfo.SPLANSTARTTIME);
            DB.AddInParameter(cmdUpdate, "SPLANENDTIME_NEW", DbType.DateTime, scheduleInfo.SPLANENDTIME);
            DB.AddInParameter(cmdUpdate, "SPLANTIMESTATE_NEW", DbType.String, scheduleInfo.SPLANTIMESTATE);
            DB.AddInParameter(cmdUpdate, "SPLANMEMO_NEW", DbType.String, scheduleInfo.SPLANMEMO);
            DB.AddInParameter(cmdUpdate, "SCHEDULETYPE_NEW", DbType.String, scheduleInfo.SCHEDULETYPE);
            DB.AddInParameter(cmdUpdate, "SPLANEMPLOYEE_NEW", DbType.String, scheduleInfo.SPLANEMPLOYEE);
            DB.AddInParameter(cmdUpdate, "SCHEDUEID", DbType.Int32, scheduleInfo.SCHEDUEID);
            DB.ExecuteNonQuery(cmdUpdate);

            if (scheduleCustomOld != null)
            {
                DeleteScheduleScheduleCustom(scheduleCustomOld);
                InsertScheduleScheduleCustom(scheduleCustomNew);
            }
        }


        /// <summary>
        /// 根据状态获取预约信息
        /// </summary>
        /// <param name="scheduleState"></param>
        /// <returns></returns>
        public DataSet QueryScheduleState(string scheduleState)
        {
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULE_STATE);
            DB.AddInParameter(queryComm, "STATE", DbType.String, scheduleState);
            return DB.ExecuteDataSet(queryComm);
        }
        /// <summary>
        /// 根据用户ID获取提醒信息
        /// </summary>
        /// <param name="customID">用户ID</param>
        /// <param name="scheduleState">提醒状态</param>
        /// <returns></returns>
        public DataSet QueryScheduleCustomID(int customID, string scheduleState)
        {
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULE_CUSTOMID);
            DB.AddInParameter(queryComm, "CUSTOMID", DbType.String, scheduleState);
            DB.AddInParameter(queryComm, "STATE", DbType.Int32, customID);
            return DB.ExecuteDataSet(queryComm);
        }
        /// <summary>
        /// 根据信息获取ScheduleID
        /// </summary>
        /// <param name="scheduleInfo">信息类</param>
        /// <returns></returns>
        private DataSet QueryScheduleGetScheduleID(DBEntitySchedule scheduleInfo)
        {
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULE_GETSCHEDULEID);
            DB.AddInParameter(queryComm, "SALONID", DbType.Int32, scheduleInfo.SALONID);
            DB.AddInParameter(queryComm, "SCHEDULENAME",  DbType.String, scheduleInfo.SCHEDULENAME);
            DB.AddInParameter(queryComm, "SCHEDULETIME", DbType.DateTime, scheduleInfo.SCHEDULETIME);
            DB.AddInParameter(queryComm, "SYSTIME",  DbType.DateTime, scheduleInfo.SYSTIME);
            DB.AddInParameter(queryComm, "STATE", DbType.String, scheduleInfo.STATE);
            DB.AddInParameter(queryComm, "SPLANSTARTTIME", DbType.DateTime, scheduleInfo.SPLANSTARTTIME);
            DB.AddInParameter(queryComm, "SPLANENDTIME", DbType.DateTime, scheduleInfo.SPLANENDTIME);
            DB.AddInParameter(queryComm, "SPLANTIMESTATE", DbType.String, scheduleInfo.SPLANTIMESTATE);
            DB.AddInParameter(queryComm, "SCHEDULETYPE", DbType.String, scheduleInfo.SCHEDULETYPE);
            DB.AddInParameter(queryComm, "SPLANEMPLOYEE", DbType.String, scheduleInfo.SPLANEMPLOYEE);
            return DB.ExecuteDataSet(queryComm);
        }
        /// <summary>
        /// 根据用户ID以及当前时间查询提醒信息.
        /// </summary>
        /// <param name="customID">用户ID</param>
        /// <param name="currTime">当前时间</param>
        /// <returns>返回提醒数据集</returns>
        public DataSet QueryScheduleCustomDateTime(int customID, DateTime currTime)
        {
            //前一个月 的第一天
            DateTime beforeMonth = currTime.AddMonths(-1);
            beforeMonth = new DateTime(beforeMonth.Year, beforeMonth.Month, 1);

            //下一个月 最后一天
            DateTime afterMonth = currTime.AddMonths(1);
            afterMonth = new DateTime(afterMonth.Year, afterMonth.Month, DateTime.DaysInMonth(afterMonth.Year, afterMonth.Month));
            
            
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULECUSTOM_DATETIMECUSTOMID);
            DB.AddInParameter(queryComm, "CUSTOMID", DbType.Int32, customID);
            DB.AddInParameter(queryComm, "BEGINTIME", DbType.DateTime, beforeMonth);
            DB.AddInParameter(queryComm, "ENDTIME", DbType.DateTime, afterMonth);
            return DB.ExecuteDataSet(queryComm);
        }
        /// <summary>
        /// 根据月份查询预约记录
        /// </summary>
        /// <param name="queryYear">查询年</param>
        /// <param name="queryMonth">查询月</param>
        /// <returns></returns>
        public DataSet QueryScheduleMonth(int queryYear, int queryMonth)
        {
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULE_DATETIME_MONTH);
            DB.AddInParameter(queryComm, "YEAR", DbType.Int32, queryYear);
            DB.AddInParameter(queryComm, "MONTH", DbType.Int32, queryMonth);
            return DB.ExecuteDataSet(queryComm);
        }
        /// <summary>
        /// 根据日查询预约记录
        /// </summary>
        /// <param name="queryYear">年</param>
        /// <param name="queryMonth">月</param>
        /// <param name="queryDay">日</param>
        /// <returns></returns>
        public DataSet QueryScheduleDay(int queryYear, int queryMonth, int queryDay)
        {
            DbCommand queryComm = DB.GetSqlStringCommand(ScheduleDAL_Const.SQL_QUERY_SCHEDULE_DATETIME_DAY);
            DB.AddInParameter(queryComm, "YEAR", DbType.Int32, queryYear);
            DB.AddInParameter(queryComm, "MONTH", DbType.Int32, queryMonth);
            DB.AddInParameter(queryComm, "DAY", DbType.Int32, queryDay);
            return DB.ExecuteDataSet(queryComm);
        }

        /// <summary>
        /// 根据时间查询以及类型查询提醒信息
        /// </summary>
        /// <param name="queryYear"></param>
        /// <param name="queryMonth"></param>
        /// <param name="enumState"></param>
        /// <returns></returns>
        public DataSet QueryScheduleCallInfoForMonth(int queryYear, int queryMonth, string enumState)
        {
            string stateFilter = " AND STATE = '" + enumState + "'";
            if (enumState.Equals(string.Empty))
            {
                stateFilter = "";
            }

            DbCommand queryComm = null;// DB.GetSqlStringCommand(SQL_QUERY_SCHEDULECALL_MONTH_STATE + stateFilter);
            DB.AddInParameter(queryComm, "YEAR", DbType.Int32, queryYear);
            DB.AddInParameter(queryComm, "MONTH", DbType.Int32, queryMonth);
            return DB.ExecuteDataSet(queryComm);
        }
        #endregion
    }
}
